const Router = require('koa-router');
const router = new Router();
const {db, QueryTypes, } = require('../models/db')
const tool = require('../tools/tool')
const biz = require('../tools/biz')

// 拜访统计
router.post('/report/bfTj', async ctx => {
    let tid = ctx.user.tenantId;
    let formData = ctx.request.body.formData;
    let currentPage = ctx.request.body.currentPage;
    let pageSize = ctx.request.body.pageSize;
    let offset = (currentPage - 1) * pageSize;
    let beg = formData.beg
    let end = formData.end
    let deptId = formData.deptId
    let buName = formData.buName
    // let areaId = formData.areaId
    let salesId = formData.salesId
    let billDate = []
    if(beg) {
        billDate.push(`billDate >= '${beg}'`)
    }
    if(end) {
        billDate.push(`billDate <= '${end}'`)
    }
    let billDateStr = billDate.join(' and ')
    if(billDateStr) {
        billDateStr = ' and ' + billDateStr
    }
    let sql = `select * from contact_bf where tenantId=${tid} and state='已完成' ${billDateStr} `
    if(salesId) {
        sql += ` and salesId=${salesId} `
    } else {
        if(deptId) {
            let dept = await db.models['dept'].findOne({
                where: {
                    id: deptId
                },
                attributes: ['code']
            })
            let userIds = ` select a.id from admin a inner join (select id from dept where code like '${dept.code}%') b on a.deptId=b.id `
            sql = ` select a.* from (${sql}) a inner join (${userIds}) b on a.salesId=b.id `
        }
    }
    if(buName) {
        let contact = ` select id from contact where tenantId=${tid} and name like '%buName%' `
        sql += ` left join ${contact} c on a.buId=c.id `
    }
    // 拜访数
    let bfs = ` select salesId  from (${sql}) a group by salesId  ` // 有拜访的业务员
    let bfsywy = `select salesId, count(*) total from (${sql}) a group by salesId ` // 业务员的拜访数量
    let bfsywykh = `select salesId,count(*) total from (select salesId, buId from (${sql}) a group by salesId,buId) a group by salesId` // 业务员的拜访客户数量
    let salesAvg = `select salesId, avg(duration) total from (select  billDate,salesId,sum(cast(duration as int )) duration from (${sql}) a group by billDate,salesId) a group by  salesId `
    let salesAvgContact = `select salesId, avg(duration) total from (select  buId,salesId,sum(cast(duration as int )) duration from (${sql}) a group by buId,salesId) a group by salesId `
    let allc = `select salesId,count(*) total from contact where tenantId=${tid} and isDel=0 group by salesId`
    let allsql = ` select a.*,co.total contacts,b.total bfNo,c.total bfKhNo,round(isnull(d.total,0)*1.0/3600,2) avgDay,round(isnull(e.total,0)*1.0/60,2) avgContact from (${bfs}) a
                  inner join (${allc}) co on a.salesId=co.salesId
                  left join (${bfsywy}) b on a.salesId=b.salesId  left join (${bfsywykh}) c on a.salesId=c.salesId
                 left join (${salesAvg}) d on a.salesId=d.salesId left join (${salesAvgContact}) e on a.salesId=e.salesId `

    allsql = `select a.*,b.name salesName from (${allsql}) a inner join admin b on a.salesId=b.id `

    let pagesql = ` ${allsql} order by a.bfKhNo desc   `
    let list =  await db.query(pagesql, {type: QueryTypes.SELECT })
    //
    let pt = `select count(*) total from (${allsql}) a  `
    let ptotal = await db.query(pt, {type: QueryTypes.SELECT, plain: true})
    let sets = {}
    let gp = {}
    if(beg && end) {
        if(tool.dayjs(end).diff(beg)/1000/3600/24 < 7) {
            let sql = `select salesId,longitude,latitude,billDate,lastDistance from contact_bf where tenantId=${tid} and billDate between '${beg}' and '${end}' and state='已完成' order by id asc `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            // 仓库坐标
            sets = await db.models['contactBfSets'].findOne({
                where: {
                    tenantId: tid
                },
                attributes: ['longitude', 'latitude']
            })
            gp = tool.xe.groupBy(ret, 'salesId')
        }
    }
    try {
        list.map(row => {
            row.unBfKhNo = (row.contacts?tool.xe.toNumber(row.contacts):0 ) - (row.bfKhNo?tool.xe.toNumber(row.bfKhNo):0)
            row.bfFgl =  tool.xe.round(tool.xe.divide(row.bfKhNo, row.contacts) * 100, 2)
            let sid = row.salesId.toString()
            let detail = tool.xe.groupBy(gp[sid], 'billDate')
            let dis_lh = 0 // 来回公里
            let dis_bf = 0 // 拜访总距离
            tool._.forEach(detail, (value, key) => {
                if(value.length > 0) {
                    let first = value[0]
                    let last = value[value.length - 1]
                    if(sets.longitude && sets.latitude && first.longitude && first.latitude && last.longitude && last.latitude) {
                        let l = tool.getDistance(sets.longitude, sets.latitude, first.longitude, first.latitude)
                        let h = tool.getDistance(sets.longitude, sets.latitude, last.longitude, last.latitude)
                        dis_lh = tool.xe.add(dis_lh, l)
                        dis_lh = tool.xe.add(dis_lh, h)
                    }
                    value.map(val => {
                        dis_bf = tool.xe.add(dis_bf, val.lastDistance)
                    })
                }
            })
            let lh = tool.xe.round(tool.xe.divide(dis_lh, 1000),2)
            let bf = tool.xe.round(tool.xe.divide(dis_bf, 1000),2)
            let all = tool.xe.round(tool.xe.divide(tool.xe.add(dis_lh, dis_bf), 1000),2)
            row.dis_lh = lh ? lh : ''
            row.dis_bf = bf ? bf : ''
            row.dis_all = all ? all : ''
        })
    }
    catch (e) {

    }
    let ret = {
        total:  ptotal.total,
        list: list,
        sets: sets
    }
    return ctx.suc('', ret)
})
// 今日任务完成情况
router.post('/report/todayBfTask', async ctx => {
    let tid = ctx.user.tenantId;
    let taskNo = 0;
    // 今日任务数量
    let taskNoModel = await db.models['contactBfSets'].findOne({
        where: {
            tenantId: tid
        }
    })
    if(taskNoModel) {
        taskNo = taskNoModel.taskNo
    }
    let today = tool.getDay()
    let sql = `
     select a.salesId,${taskNo} taskNo, a.bfNo,b.name salesName from (
         select salesId,count(*) bfNo from (select salesId,buId from contact_bf where tenantId=${tid} and bilLDate='${today}' and state='已完成' group by salesId, buId) a group by salesId
     ) a inner join admin b on a.salesId=b.id order by a.bfNo desc `
    let bfsModel = await db.query(sql, {type: QueryTypes.SELECT})
    bfsModel.map(row => {
        row.wcl = tool.xe.toNumber(row.taskNo) === 0 ? 0 : tool.xe.round( (row.bfNo*1.0 / row.taskNo)* 100, 2)
    })
    return ctx.suc('', bfsModel)

})
// 简报
router.post('/report/jb', async ctx => {
    let obj = {
        addNo: 0,
        bfNo: 0
    }
    let tid = ctx.user.tenantId;
    // 新增客户数量
    let addNo = await db.query(`select count(*) total from contact where tenantId=${tid} and convert(varchar(10), ctime, 120)='${tool.getDay()}' `, {type: QueryTypes.SELECT, plain: true})
    let bfNo = await db.query(`select count(*) total from contact_bf where tenantId=${tid} and billDate='${tool.getDay()}' `, {type: QueryTypes.SELECT, plain: true})
    obj.addNo = addNo.total
    obj.bfNo = bfNo.total
    return ctx.suc('', obj)
})
// 排行
router.post('/report/ph', async ctx => {
    let tid = ctx.user.tenantId;
    // 新增客户排行
    let khs = `
     select top 20 b.name,qty from (select salesId,count(*) qty from contact where tenantId=${tid} and convert(varchar(10), ctime, 120)='${tool.getDay()}' group by salesId) a inner join admin b on 
         a.salesId=b.id order by qty desc 
     `
    let bfs = `
     select top 20 b.name,qty from (select salesId,count(*) qty from contact_bf where tenantId=${tid} and billDate='${tool.getDay()}' group by salesId) a inner join admin b on 
         a.salesId=b.id order by qty desc 
     `
    let qtyKh = await db.query(khs, {type: QueryTypes.SELECT })
    let qtyBf = await db.query(bfs, {type: QueryTypes.SELECT })
    let obj = {
        qtyKh: qtyKh,
        qtyBf: qtyBf
    }
    return ctx.suc('', obj)
})
// 客户数
router.post('/report/todayKhs', async ctx => {
    let today = tool.getDay('')
    let ret = await biz.todayKhs(ctx, today)
    return ctx.suc('', ret)
})
// 排行实时
router.post('/report/todayPh', async ctx => {
    let today = tool.getDay('')
    let list = await biz.todayPh(ctx, today)
    return ctx.suc('', list)
})

// 正在拜访中的数据
router.post('/report/todayUndone', async ctx => {
    let today = tool.getDay('')
    let list = await biz.todayUndone(ctx, today)
    return ctx.suc('', list)
})





module.exports = router;
